﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Linq;
using System.Collections.Generic;
using System.IO.IsolatedStorage;
using LocalDBDemo;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections;
namespace Family
{
    public class DBHelper
    {
        private static Dictionary<char, char> dictionary = new Dictionary<char, char>
            {
                { 'á', 'a' },
                { 'à', 'a' },
                { 'ả', 'a' },
                { 'ã', 'a' },
                { 'ạ', 'a' },
                { 'â', 'a' },
                { 'ấ', 'a' },
                { 'ầ', 'a' },
                { 'ẩ', 'a' },
                { 'ẫ', 'a' },
                { 'ậ', 'a' },
                { 'ă', 'a' },
                { 'ắ', 'a' },
                { 'ằ', 'a' },
                { 'ẳ', 'a' },
                { 'ẵ', 'a' },
                { 'ặ', 'a' },
                { 'é', 'e' },
                { 'è', 'e' },
                { 'ẻ', 'e' },
                { 'ẽ', 'e' },
                { 'ẹ', 'e' },
                { 'ê', 'e' },
                { 'ế', 'e' },
                { 'ề', 'e' },
                { 'ể', 'e' },
                { 'ễ', 'e' },
                { 'ệ', 'e' },
                { 'í', 'i' },
                { 'ì', 'i' },
                { 'ỉ', 'i' },
                { 'ĩ', 'i' },
                { 'ị', 'i' },
                { 'ó', 'o' },
                { 'ò', 'o' },
                { 'ỏ', 'o' },
                { 'õ', 'o' },
                { 'ọ', 'o' },
                { 'ô', 'o' },
                { 'ố', 'o' },
                { 'ồ', 'o' },
                { 'ổ', 'o' },
                { 'ỗ', 'o' },
                { 'ộ', 'o' },
                { 'ơ', 'o' },
                { 'ớ', 'o' },
                { 'ờ', 'o' },
                { 'ở', 'o' },
                { 'ỡ', 'o' },
                { 'ợ', 'o' },
                { 'ú', 'u' },
                { 'ù', 'u' },
                { 'ủ', 'u' },
                { 'ũ', 'u' },
                { 'ụ', 'u' },
                { 'ư', 'u' },
                { 'ứ', 'u' },
                { 'ừ', 'u' },
                { 'ử', 'u' },
                { 'ữ', 'u' },
                { 'ự', 'u' },
                { 'ý', 'y' },
                { 'ỳ', 'y' },
                { 'ỷ', 'y' },
                { 'ỹ', 'y' },
                { 'ỵ', 'y' },
                { 'đ', 'd' },
            };
        private const string ConnectionString = @"Data Source=isostore:/CookingCe.sdf";
        public static void MoveReferenceDatabase()
        {
            // Obtain the virtual store for the application.
            IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();

            // Create a stream for the file in the installation folder.
            using (Stream input = Application.GetResourceStream(new Uri("CookingCe.sdf", UriKind.Relative)).Stream)
            {
                // Create a stream for the new file in isolated storage.
                using (IsolatedStorageFileStream output = iso.CreateFile("CookingCe.sdf"))
                {
                    // Initialize the buffer.
                    byte[] readBuffer = new byte[4096];
                    int bytesRead = -1;

                    // Copy the file from the installation folder to isolated storage. 
                    while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0)
                    {
                        output.Write(readBuffer, 0, bytesRead);
                    }
                }
            }
        }
        public static void CreateDatabase()
        {
            using (var context = new DataContext(ConnectionString))
            {
                if (!context.DatabaseExists())
                {
                    context.CreateDatabase();
                }
            }
        }
        public static void DeleteDatabase()
        {
            using (var context = new DataContext(ConnectionString))
            {
                if (context.DatabaseExists())
                {
                    // delete database if it exist
                    context.DeleteDatabase();
                }
            }
        }
        public static IList<Kind> FindKindbyID(int id)
        {
            IList<Kind> kind = null;
            using (DataContext context = new DataContext(ConnectionString))
            {
                IQueryable<Kind> query = from c in context.Kinds 
                                         where c.Id == id                                       
                                         select c;
                kind = query.ToList();
            }
            return kind;
        }
        public static void AddnewKind(Kind kind)
        {

            using (var context = new DataContext(ConnectionString))
            {
                if (context.DatabaseExists())
                {
                    context.Kinds.InsertOnSubmit(kind);
                    context.SubmitChanges();
                }
            }
        }
        public static void AddNewFood(Food food)
        {
            using (var context = new DataContext(ConnectionString))
            {
                if (context.DatabaseExists())
                {
                    context.Foods.InsertOnSubmit(food);
                    context.SubmitChanges();
                }
            }
        }
        public static string FormatInputData(string strInput)
        {
            string strOutput = String.Empty;
            try
            {
                string strLowerInput = strInput.ToLower();
                strLowerInput = strLowerInput.Trim();
                strLowerInput = Regex.Replace(strLowerInput, @"\s+", " ");
                foreach (char c in strLowerInput)
                {
                    if (dictionary.ContainsKey(c))
                    {
                        strOutput += dictionary[c];
                    }
                    else
                    {
                        strOutput += c;
                    }
                }
            }
            catch (System.Exception ex)
            {
                strOutput = String.Empty;
            }

            return strOutput;
        }
        public static IList<Food> GetAllFood()
        {
            IList<Food> food;
            using (DataContext datacontext = new DataContext(ConnectionString))
            {
                IQueryable<Food> query = from c in datacontext.Foods select c;
                food = query.ToList();
            }
            return food;
        }
        public static IList<Food> FindFoodByName(string input)
        {
            IList<Food> food = new List<Food>();
            IList<Food> Source = GetAllFood();
            for (int i = 0; i < Source.Count; i++)
            {
                string usignedName = FormatInputData(Source[i].Name);
                if (usignedName == input)
                    food.Add(Source[i]);
            }
                return food;
        }
        public static IList<Country> GetAllCountry()
        {
            IList<Country > country = null;
            using (DataContext datacontext = new DataContext(ConnectionString))
            {
                IQueryable<Country> query = from c in datacontext.Countries select c;
                country = query.ToList();
            }
            return country;
        }
        public static IList<Category> GetAllCategory()
        {
            IList<Category> category = null;
            using (DataContext datacontext = new DataContext(ConnectionString))
            {
                IQueryable<Category> query = from c in datacontext.Categories select c;
                category = query.ToList();
            }
            return category;
        }
        public static IList<Food> FindFood(string name, List<Country> country, List<Category> category)
        {
            IList<Food> food = new List<Food>();
            IList<Food> allFood = GetAllFood();
            if (name == "")
            {
                for (int i = 0; i < allFood.Count; i++)
                {
                    if (CompareCountryId(allFood[i].Id, country) == true && CheckExistFood(allFood[i].Id, food))
                        food.Add(allFood[i]);


                }
              
            }
            else 
            { }
            return food;
        }
        public static bool CompareCategoryId(int id, List<Category> category)
        {
            for (int i = 0; i < category.Count; i++)
            {
                if(id == category[i].Id)
                return true;
            }
            return false;
        }
        public static bool CompareCountryId(int id, List<Country> country)
        {
            for (int i = 0; i < country.Count; i++)
            {
                if (id == country[i].Id)
                    return true;
            }
            return false;
        }
        public static bool CheckExistFood(int id, IList<Food> food)
        {
            for (int i = 0; i < food.Count; i++)
            {
                if (id == food[i].Id)
                    return true;
            }
            return false;
        }        

    }
}
